package fxification.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class SqlTestHelper{
    public static void createTables(Connection conn) throws Exception{
        StringBuffer sql = new StringBuffer();
        sql
            .append("CREATE table TEST_TBL (")
            .append("ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),")
            .append("FIRSTNAME   VARCHAR(30),")
            .append("LASTNAME    VARCHAR(30),")
            .append("PHONE       VARCHAR(20),")
            .append("EMAIL       VARCHAR(30),")
            .append("TITLE       VARCHAR(30))");

            conn.createStatement().execute(sql.toString());
    }
    
    public static void populateData(Connection conn) throws Exception{
        StringBuffer sql = new StringBuffer();
        sql
            .append("INSERT INTO TEST_TBL(FIRSTNAME, LASTNAME, PHONE, EMAIL, TITLE)")
            .append("VALUES(?,?,?,?,?)");

        PreparedStatement stmt = conn.prepareStatement(sql.toString());
        
        stmt.setObject(1, "Vladimir");
        stmt.setObject(2, "Vivien");
        stmt.setObject(3, "18881234567");
        stmt.setObject(4, "test.eamil@gmail.com");
        stmt.setObject(5, "Developer");
        
        stmt.executeUpdate();
    }

    public static void detroyTable(Connection conn) throws Exception{
        try{
            conn.createStatement().execute("DROP table TEST_TBL");
        }catch(SQLException ex){
            System.out.println ("Unable to drop table: " + ex.getMessage());
        }
    }

}
